using Query, DataFrames

df = DataFrame(name=["John", "Sally", "Kirk"],
               age=[23., 42., 59.],
               children=[3,5,2])

x = df |>
    @filter(_.age>50) |>
    @map({_.name, _.children}) |>
    DataFrame

println(x)

q = @from <range variable> in <source> begin
    <query statements>
end

x = @from i in df begin
    @where i.age>50
    @select {i.name, i.children}
    @collect DataFrame
    # 注意LINQ中用@collect 定义输出结果的格式
end
println(x)

x = df |> @query(i, begin
            @where i.age>50
            @select {i.name, i.children}
        end) |> DataFrame
println(x)

df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
df2 = DataFrame(c=[2,4,2], d=["John", "Jim", "Sally"])

x = df1 |> @groupjoin(df2, _.a, _.c, {t1=_.a, t2=length(__)}) |> DataFrame

println(x)

df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
df2 = DataFrame(c=[2,4,2], d=["John", "Jim","Sally"])

x = df1 |> @join(df2, _.a, _.c, {_.a, _.b, __.c, __.d}) |> DataFrame

println(x)

source = Dict(:a=>[1,2,3], :b=>[4,5])

q = source |> @mapmany(_.second, {Key=_.first, Value=__}) |> DataFrame

println(q)

df = DataFrame(fruit=["Apple","Banana","Cherry"],
               amount=[2,6,1000],
               price=[1.2,2.0,0.4],
               isyellow=[false,true,false])

q1 = df |> @select(2:3, occursin("ui"), -:amount) |> DataFrame
# 2:3 => :amount, :price
# occursin("ui") => :amount, :price, :fruit
# -:amount => :price, :fruit
println(q1)

df = DataFrame(fruit=["Apple","Banana","Cherry"],
               amount=[2,6,1000],
               price=[1.2,2.0,0.4],
               isyellow=[false,true,false])

q = df |> @mutate(price = 2 * _.price + _.amount, isyellow = _.fruit == "Apple") |> DataFrame
println(q)

using Query, DataFrames

# Sorting:
# @orderby <attribute>[, <attribute>]
df = DataFrame(a=[2,1,1,2,1,3],b=[2,2,1,1,3,2])
x = @from i in df begin
    @orderby descending(i.a), i.b
    @select i
    @collect DataFrame
end

# Filtering
# @where <condition>
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,5,2])
x = @from i in df begin
    @where i.age > 30. && i.children > 2
    @select i
    @collect DataFrame
end

# Projecting
# @select <condition>
data = [1,2,3]
x = @from i in data begin
    @select i^2
    @collect
end
# query中应用`{}`可以将元素转换成命名元组
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,5,2])
x = @from i in df begin
    @select {i.name, Age=i.age} # 不定义名称时, 自动推断name; 显式声明名称Age;
    @collect DataFrame
end

# Flattening
# 使用多个@from实现:
# @from <range_var> in <selector>
source = Dict(:a=>[1,2,3], :b=>[4,5])
q = @from i in source begin
    @from j in i.second
    @select {Key=i.first,Value=j}
    @collect DataFrame
end

# Joining
# @join <range variable> in <source> on <left key> equals <right key>
df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
df2 = DataFrame(c=[2,4,2], d=["John", "Jim","Sally"])

x = @from i in df1 begin
    @join j in df2 on i.a equals j.c
    @select {i.a,i.b,j.c,j.d}
    @collect DataFrame
end

# Group join
# @join <range variable> in <source> on <left key> equals <right key> into <group variable>
df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
df2 = DataFrame(c=[2,4,2], d=["John", "Jim","Sally"])

x = @from i in df1 begin
    @join j in df2 on i.a equals j.c into k
    @select {t1=i.a,t2=length(k)}
    @collect DataFrame
end

# Left outer join
# @left_outer_join <range variable> in <source> on <left key> equals <right key>
source_df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
source_df2 = DataFrame(c=[2,4,2], d=["John", "Jim","Sally"])

q = @from i in source_df1 begin
    @left_outer_join j in source_df2 on i.a equals j.c
    @select {i.a,i.b,j.c,j.d}
    @collect DataFrame
end

# Grouping
# @group <element selector> by <key selector> [into <range variable>]
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,2,2])
x = @from i in df begin
    @group i.name by i.children
    @collect
end
# with into:
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,2,2])
x = @from i in df begin
    @group i by i.children into g
    @select {Key=key(g),Count=length(g)}
    @collect DataFrame
end

# Split-Apply-Combine a.k.a dplyr
# @select new_var = agg_fun(g.var)
# agg_fun是聚合函数, 如mean, g是分组, var是要汇总的列
df = DataFrame(name=repeat(["John", "Sally", "Kirk"],
               inner=[1],outer=[2]), 
               age=vcat([10., 20., 30.],[10., 20., 30.].+3), 
               children=repeat([3,2,2],inner=[1],outer=[2]),
               state=[:a,:a,:a,:b,:b,:b])

x = @from i in df begin
    @group i by i.state into g
    @select {group=key(g),mage=mean(g.age), oldest=maximum(g.age), youngest=minimum(g.age)}
    @collect DataFrame
end

# Range variables
# @let <range variable> = <value selector>
df = DataFrame(name=["John", "Sally", "Kirk"],
               age=[23., 42., 59.], 
               children=[3,2,2])

x = @from i in df begin
    @let count = length(i.name)
    @let kids_per_year = i.children / i.age
    @where count > 4
    @select {Name=i.name, Count=count, KidsPerYear=kids_per_year}
    @collect DataFrame
end

spec = data("cars") |> @vlplot(:point, x=:Acceleration, y=:Cylinders)
spec.mark
spec.encoding.x.field
# 使用Setfiled.jl/Accessors.jl的@set更改属性:
using Setfield  # imports `@set` etc.
spec2 = @set spec.mark = :line
spec3 = @set spec2.encoding.y.field = "Miles_per_Gallon"

# 根据后缀名判断输出文件格式
p |> save("figure.png")
p |> save("figure.svg")
p |> save("figure.pdf")
p |> save("figure.eps")
p |> save("figure.vegalite")

save("figure.png", p)
save("figure.svg", p)
save("figure.pdf", p)
save("figure.eps", p)
save("figure.html", p)

using DataFrames, DataVoyager

data = DataFrame(a=rand(100), b=randn(100))

v1 = data |> Voyager()
v2 = Voyager(data)

# 用 `[]` 提取作图结果:
plot1 = v1[] # plot1 is a VLSpec
# 保存和重载:
v[] |> save("figure1.vegalite")
dataset("cars") |> load("figure1.vegalite")